/ **
*点击上面三角按钮即可执行
*参数说明
* ！！！！！！！设置is_delete为true，则删除表后再创建！！！！！！！
* ！！！！！！！设置is_batch为false，则根据当前选中的sheet建表！！！！！！！
* /

var
is_delete = true // 建表前，删除表
var
is_batch = false // 批量建表

var
create_sqls = [] // 建表语句
var
delete_sqls = [] // 删表语句
if (is_batch){
// 跳过不执行的sheet
var skips=["目录", "机构信息基础表"]
// 遍历并打印所有工作表的名称
Application.Sheets.Each((item) = > {
if (skips.includes(item.Name)){
console.log("《"+item.Name+"》不是业务表，跳过")
} else {
// 切换名称工作表
Application.Sheets.Item(item.Name).Activate()
if (Application.Range("C2").Text){
generate_sql()
}
}
})
} else {
generate_sql()
}

// 连接MySQL数据库
const
connection = SQL.connect(SQL.Drivers.MySQL, {
    host: '116.204.71.143',
    username: 'fjsbb',
    password: 'fjsbb',
    database: 'fjsbb',
    port: 3306
})

if (is_delete){
// 循环删除表
delete_sqls.forEach(sql= > {
console.log(sql)
const result = connection.queryAll(sql)
console.log(result)
})
}

// 循环创建表
create_sqls.forEach(sql= > {
    console.log(sql)
const
result = connection.queryAll(sql)
console.log(result)
})

// 关闭数据库连接
connection.close()

// 生成sql语句
function
generate_sql()
{
    var
ziduan_index = 13 // 字段起始位置
var
table_name_column = Application.Range("C2").Text // 表名
var
table_comment_column = Application.Range("C3").Text // 表注释
var
unid_column = Application.Range("B12").Text // 主键
var
unid_data_type_column = Application.Range("C12").Text // 主键
var
unid_comment_column = Application.Range("D12").Text // 主键
var
delete_sql = "DROP TABLE IF EXISTS `" + table_name_column + "`;"
var
create_sql = "CREATE TABLE IF NOT EXISTS `" + table_name_column + "` (`" + unid_column + "` " + unid_data_type_column + "(255) PRIMARY KEY COMMENT '" + unid_comment_column + "',"
for (let row = ziduan_index; row <= 1000; row++) {
    col_column=Application.Range("B" + row).Text
col_data_type_column=Application.Range("C" + row).Text
col_comment_column=Application.Range("D" + row).Text
if (!col_column){
break
}
if (col_data_type_column.toLowerCase() ==='varchar'){
create_sql += " `" + col_column + "` " + col_data_type_column + "(255) DEFAULT NULL COMMENT '" + col_comment_column + "',"
} else if (col_data_type_column.toLowerCase() ==='int'){
create_sql += " `" + col_column + "` " + col_data_type_column + "(8) DEFAULT NULL COMMENT '" + col_comment_column + "',"
} else {
create_sql += " `" + col_column + "` " + col_data_type_column + " DEFAULT NULL COMMENT '" + col_comment_column + "',"
}

}
create_sql = create_sql.substring(0,
                                  create_sql.length - 1) + " )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='" + table_comment_column + "';"

create_sqls.push(create_sql)
delete_sqls.push(delete_sql)
}